package com.tfkj.repository;

import com.tfkj.entity.Student;
import com.tfkj.vo.ExcelRelVO;
import com.tfkj.vo.RelAndImageVO;
import com.tfkj.vo.RelVO;
import org.apache.ibatis.annotations.*;

import java.time.LocalDateTime;
import java.util.List;

@Mapper
public interface StudentRepository {

    @Select("""
        <script>
            select * from schooltest.student a
            where role = #{role}
              <if test='className != null and className != ""'>
                AND FIND_IN_SET(a.className, #{className}) > 0
              </if>
              <if test='name != null'>
                AND a.name LIKE #{name}
              </if>
            LIMIT ${pageSize} OFFSET ${offset}
        </script>
    """)
    List<Student> findAllMyUser(@Param("role") String role, @Param("className") String className, @Param("name") String name, @Param("offset") Integer offset, @Param("pageSize") Integer pageSize);

    @Select("select * from schooltest.student where `id` = ${id}")
    Student findUserById(Integer id);

    @Select("select * from schooltest.student where studentNum = #{studentNum} AND password = #{password}")
    Student findUserByLoginInfo(@Param("studentNum") String studentNum, @Param("password") String password);

    @Insert("<script>" +
            "INSERT INTO schooltest.student_image_rel (studentNum, imageId) " +
            "VALUES " +
            "<foreach collection='list' item='item' separator=','>" +
            "(#{studentNum}, " +
            "(SELECT id FROM schooltest.image WHERE testId = #{testId} AND sortNum = #{item}))" +
            "</foreach>" +
            "</script>")
    Integer insertStudentImageRel(@Param("studentNum") String studentNum, @Param("testId") Integer testId, @Param("sortNum") List<Integer> sortNum);

    @Update("update schooltest.student_image_rel set isError = ${isError} where id = ${id}")
    Integer updateStudentImageRel(@Param("isError")Integer isError, @Param("id")Integer id);

    @Update("update schooltest.student set password = #{password} where studentNum = #{studentNum} AND password = #{oldPassword}")
    Integer updatePassword(@Param("studentNum")String studentNum, @Param("password")String password, @Param("oldPassword")String oldPassword);

    @Delete("delete from schooltest.student_image_rel where `id` = ${id}")
    void deleteRel(@Param("id") Integer id);

    @Select("""
        <script>
            select COUNT(*) from schooltest.student a
            where role = #{role}
              <if test='className != null and className != ""'>
                AND FIND_IN_SET(a.className, #{className}) > 0
              </if>
              <if test='name != null'>
                AND a.name LIKE #{name}
              </if>
        </script>
    """)
    Integer findAllMyUserTotal(@Param("role")String role,@Param("className")String className,@Param("name")String name);

    @Insert("""
        <script>
            insert into schooltest.student(name, grade, className, role, studentNum, password
              <if test='subject != null and subject != ""'>
                , subject
              </if>
        ) values (#{name}, #{grade}, #{className}, #{role}, #{studentNum},  #{studentNum}
              <if test='subject != null and subject != ""'>
                , #{subject}
              </if>
              )
        </script>
    """)
    void createStudent(@Param("grade") String grade, @Param("name") String name, @Param("className") String className, @Param("role") String role, @Param("studentNum") String studentNum, @Param("subject") String subject);

    @Update("update schooltest.student set phone = #{phone}, email = #{email} where studentNum = #{studentNum}}")
    void updateInfo(@Param("studentNum") String studentNum, @Param("phone") String phone, @Param("email") String email);


    @Select("""
    <script>
        SELECT b.id, b.url, b.grade, b.subject, b.questionType, b.difficulty, d.name
        FROM schooltest.student_image_rel a
        LEFT JOIN schooltest.image b ON a.imageId = b.id
        LEFT JOIN schooltest.test c ON b.testId = c.id
        LEFT JOIN schooltest.student d ON d.studentNum = a.studentNum
        WHERE a.isError = 1
         <if test='questionTypes != null and !questionTypes.isEmpty()'>
           AND
           <foreach item='type' collection='questionTypes' separator=' AND '>
             CONCAT(',', questionType, ',') LIKE CONCAT('%,', #{type}, ',%')
           </foreach>
         </if>
          <if test='studentNum != null'>
            AND a.studentNum = ${studentNum}
          </if>
          <if test='grade != null'>
            AND d.grade = #{grade}
          </if>
          <if test='className != null'>
            AND #{className} LIKE CONCAT('%', d.className, '%')
          </if>
          <if test='subject != null'>
            AND c.subject = #{subject}
          </if>
          <if test='difficulty != null'>
            AND b.difficulty = ${difficulty}
          </if>
          <if test='startTime != null'>
            AND a.createTime &gt;= #{startTime}
          </if>
          <if test='endTime != null'>
            AND a.createTime &lt;= #{endTime}
          </if>
        ORDER BY
            (SELECT COUNT(*)
             FROM schooltest.student_image_rel
             WHERE testId = b.testId AND sortNum = b.sortNum) DESC
        LIMIT ${pageSize} OFFSET ${offset}
    </script>
""")
    List<RelVO> findStudentImageRel(
            @Param("studentNum") String studentNum,
            @Param("grade") String grade,
            @Param("className") String className,
            @Param("subject") String subject,
            @Param("questionTypes") List<String> questionTypes,
            @Param("difficulty") Integer difficulty,
            @Param("offset") Integer offset,
            @Param("pageSize") Integer pageSize,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime
    );



    @Select("""
    <script>
        SELECT COUNT(*)
        FROM schooltest.student_image_rel a
        LEFT JOIN schooltest.image b ON a.imageId = b.id
        LEFT JOIN schooltest.test c ON b.testId = c.id
        LEFT JOIN schooltest.student d ON d.studentNum = a.studentNum
        WHERE a.isError = 1
          <if test='questionTypes != null and !questionTypes.isEmpty()'>
           AND
           <foreach item='type' collection='questionTypes' separator=' AND '>
             CONCAT(',', questionType, ',') LIKE CONCAT('%,', #{type}, ',%')
           </foreach>
         </if>
          <if test='studentNum != null'>
            AND a.studentNum = ${studentNum}
          </if>
          <if test='grade != null'>
            AND d.grade = #{grade}
          </if>
          <if test='className != null'>
            AND #{className} LIKE CONCAT('%', d.className, '%')
          </if>
          <if test='subject != null'>
            AND c.subject = #{subject}
          </if>
          <if test='difficulty != null'>
            AND b.difficulty = ${difficulty}
          </if>
          <if test='startTime != null'>
            AND a.createTime &gt;= #{startTime}
          </if>
          <if test='endTime != null'>
            AND a.createTime &lt;= #{endTime}
          </if>
    </script>
""")
     Integer findStudentImageRelTotal(
            @Param("studentNum") String studentNum,
            @Param("grade") String grade,
            @Param("className") String className,
            @Param("subject") String subject,
            @Param("questionTypes") List<String> questionTypes,
            @Param("difficulty") Integer difficulty,
            @Param("startTime") LocalDateTime startTime,
            @Param("endTime") LocalDateTime endTime
    );

    @Select("select * from schooltest.student where className LIKE #{className} AND role = '老师'")
    List<Student> findTeacherList(String className);

    @Insert({
            "<script>",
            "INSERT INTO schooltest.student_image_rel (studentNum, imageId, isError)",
            "SELECT #{studentNum}, img.id, 1",
            "FROM test t",
            "JOIN schooltest.image img ON t.id = img.testId",
            "JOIN (",
            "  <foreach collection='vos' item='vo' separator=' UNION ALL '>",
            "    SELECT ",
            "      #{vo.pageNum} AS p,",
            "      #{vo.testType} AS tt,",
            "      #{vo.sortNum} AS sn",
            "  </foreach>",
            ") params",
            "ON img.pageNum = params.p",
            "   AND img.testType = params.tt",
            "   AND img.sortNum = params.sn",
            "WHERE t.testNum = #{testNum}",
            "</script>"
    })
    void insertRelByUpload(
            @Param("testNum") String testNum,
            @Param("studentNum") String studentNum,
            @Param("vos") List<ExcelRelVO> vos
    );

    @Select("select * from schooltest.student where studentNum IN ${nums}")
    List<Student> findUserByNums(String nums);

    @Select(" select a.studentNum, b.url from schooltest.student_image_rel a " +
            " left join schooltest.image b " +
            " on a.imageId = b.id" +
            " where a.studentNum in ${users}" +
            " and a.createTime >= #{startDatetime}" +
            " and a.createTime <= #{endDatetime}")
    List<RelAndImageVO> findImages(@Param("users") String users, @Param("startDatetime") LocalDateTime startDatetime, @Param("endDatetime") LocalDateTime endDatetime);

    @Select("select studentNum from schooltest.student where className = #{className} and role = '学生'")
    List<String> findStudentIds(@Param("className") String className);
}
